from urllib.request import urlopen
import sqlite3
import re

    
    
def addCourse(code, url, FSY, meetSec, LT, time, loc, inst, cur):
    '''(String all, Cursor) -> NoneType
    Add a course and its informations into a database table.
    '''
    
    cur.execute('''INSERT INTO courses4(code, url, FSY, meetSec, LT, time, loc, inst)
    VALUES (?,?,?,?,?,?,?,?)''', (code, url, FSY, meetSec, LT, time, loc, inst))


   
def retrTime(preCour, preSec, cur):
    '''(Bytes, Bytes, Cursor) -> Bytes
    Retrive and return the time of the previous course.
    '''
    
    cur.execute('''SELECT time FROM courses4 WHERE code in (?, ?) AND meetSec=?''', (preCour[1:], preCour, preSec))
    return cur.fetchone()[0]




def courseListings():
    '''(NoneType) -> list
    Take in an url link that has all the course listings, add the prefix to the html links, and saves
    these url links as Strings in a list. Remove all the duplicate strings and text.html. Finally return
    the list of url links for the course categories.
    '''
    
    url = str(urlopen('http://www.artsandscience.utoronto.ca/ofr/timetable/winter/sponsors.htm').read())    
    
    
    urlPattern = re.compile("([a-z]*\.html)")
    
    allUrl = set(re.findall(urlPattern, url))
    allUrl.remove("text.html")
    allUrl.remove("assem.html")
    allUrl = list(allUrl)
    
    for link in range(len(allUrl)):
        allUrl[link] = "http://www.artsandscience.utoronto.ca/ofr/timetable/winter/" + allUrl[link]
    return allUrl

   
def storeCourseInfo(courseList, cur):
    '''(list, Cursor) -> NoneType
    Take in the list of url links for course categories and while parsing each link, store the course code,
    FSY, meeting section, LT (lecture/tutorial), time, location, and instructor into the database.
    '''

    # if you don't put ? after .* the algo will be greedy. So .*? will be non-greedy, which means it
    # will stop as soon as it can.
    rowPattern = re.compile('<tr>(.*?)</tr>', re.DOTALL)
    coursePattern = re.compile('<td(.*?)</td>', re.DOTALL)
    preSec = ""
    preCour = ""
    preUrl = ""
    preFSY = ""
    
    for link in courseList:
        url = str(urlopen(link).read())
        
        # allRows is a list of rows in the link
        allRows = re.findall(rowPattern, url) 
      
        for row in allRows: 
            
            # if the string row has "Cancel" or "cancel" or "CANCEL", then ignore the row.
            # otherwise, process the row entries to valid form.
            courseInfo = re.findall(coursePattern, row)
            if re.search('(Cancel|cancel|CANCEL)', row) == None and len(courseInfo) > 8:
                
                #print(courseInfo)
                #for i in courseInfo:
                    #print(i)
                #print(len(courseInfo))
                
                proUrl, proName = formatURLandName(courseInfo[0])
                FSY = formatFSY(courseInfo[1])
                proName += FSY
                sec, LT = formatSectionAndLT(courseInfo[3])
                
                time = formatTimeLocInst(courseInfo[5])
                location = formatTimeLocInst(courseInfo[6])
                inst = formatTimeLocInst(courseInfo[7])
                

                        
                
                # if only time appears, need to check whether these is a lecture section. If not, then that means the time should 
                # be bundled together with the previous time. So retrieve the course time info from the database, append the new time
                # by a comma, and put it back into the database.
                #if there is a lecture section but has no course name, with use the previous course name and FSY and add to database.                 
                if sec == '&nbsp;':
                    preTime = retrTime(preCour, preSec, cur)
                    newTime = preTime + "," + time
                    cur.execute('''UPDATE courses4 SET time=? WHERE code=? AND meetSec=?''', (newTime, preCour, preSec))          
                elif proName == '&nbsp;&nbsp;':
                    #cur.execute('''SELECT * FROM courses WHERE code=? AND FSY=? AND meetSec=?''', (preCour, preFSY, sec))
                    #for tup in cur:
                        #print(link)
                        #print(tup)
                    #fullCode = preCour + preFSY
                    addCourse(preCour, preUrl, preFSY, sec, LT, time, location, inst, cur)
                    preSec = sec
                else:
                    #fullCode2 = proName + FSY
                    addCourse(proName, proUrl, FSY, sec, LT, time, location, inst, cur)                                      
                    preSec = sec
                    preCour = "@" + proName
                    #preCour = fullCode2
                    preUrl = proUrl
                    preFSY = FSY
                    
                

  
       # <td><a href="http://www.artsandscience.utoronto.ca/ofr/1314_199/ccr199h1.html#L0021F">L0021</a></td>
        #<td>The "New" Visual Culture </td>
        #<td>University</td>
        #<strong>R10-12<br /> 
        #  (note: time change)</strong>
       # <td>&nbsp;</td>
       # <td>Tba</td>  /r/n
  
def storeSeminar(cur):
    '''
    (Cursor) -> NoneType
    '''
    #process "assem.html" stuff separately

    preSec = ""
    preCour = ""    
    
    link = str(urlopen("http://www.artsandscience.utoronto.ca/ofr/timetable/winter/assem.html").read())
    rowPat = re.compile('(<td>\s*?<a href=.*?)\s*?</tr>', re.DOTALL)
    allRows = re.findall(rowPat, link)
    urlPat = re.compile('"(http.*?)">')
    namePat = re.compile("[a-z]{3}\d{3}[hy]1")
    tdPat = re.compile('<td>(.*?)</td>', re.DOTALL)
    timePat = re.compile('>?(.*?)<?', re.DOTALL)
    LT = 'L'
    for row in allRows:
        if re.search('(Cancel|cancel|CANCEL)', row) == None:
            tdList = re.findall(tdPat,row)
            urlList = re.findall(urlPat, tdList[0])
            url = urlList[0]
            nameList = re.findall(namePat, url)
            name = nameList[0]
            
            # if the last char is a digit, then that means this course's FSY is Y , otherwise it's either F or S.
            if url[-1:].isdigit():
                FSY = 'Y'
                sec = url[-5:]
            else:
                FSY = url[-1:]
                sec = url[-6:-1]
                
            name += FSY
            location = tdList[4]
            inst = tdList[5]
            
            if location == "&nbsp;":
                location = "N/A"
            if inst == "&nbsp;":
                inst = "N/A"            
                
            # get the processed time.
            # Note: when put into the database, some time may appear in the form of "W12-2 (filming screening)"
            timeList = re.findall(timePat, tdList[3])
            time = timeList[0]
            if sec == '&nbsp;':
                preTime = retrTime(preCour, preSec, cur)
                newTime = preTime + "," + time
                cur.execute('''UPDATE courses4 SET time=? WHERE code=? AND meetSec=?''', (newTime, preCour, preSec))
            else:
                cur.execute('''SELECT * FROM courses4 WHERE code=?''', (name,))
                if cur.fetchone() == None:
                    preCour = "@" + name
                else:
                    name = "@" + name
                    preCour = name                       
                addCourse(name, url, FSY, sec, LT, time, location, inst, cur)
                preSec = sec
                
                
                
        

  
def formatURLandName(UrlAndName):
    '''
    (Bytes) -> tuple of Bytes
    Take in the unprocessed UrlAndName String and return a tuple containing the processed
    url and course code.
    '''
    
    
    urlPat = re.compile('(http.*?)>')
    namePat = re.compile("[A-Z]{3}\d{3}[HYhy][\d]")
    
    name = "&nbsp;"
    url = "&nbsp;"
    
    nameList = re.findall(namePat, UrlAndName)
    if len(nameList) == 2:
        url = re.findall(urlPat, UrlAndName)[0]
        name = nameList[0]
    elif len(nameList) == 1:
        url = "http://www.artsandscience.utoronto.ca/ofr/calendar/crs_" + nameList[0][0:2].lower() + ".htm#" + nameList[0]
        name = nameList[0]
    return (url, name)



def formatFSY(FSY):
    '''
    (Bytes) -> Bytes
    Take in unprocessed FSY and return the one-character Byte for FSY.
    '''
    
    #FSY = FSY.lstrip(">\s*?<font size =-1>")
    #FSY = FSY.rstrip("</font>")
    pat = re.compile('>([FSY]|(&nbsp;))<?')
    FSY = re.search(pat, FSY)
    return FSY.group(1)


def formatSectionAndLT(sec):
    '''
    (Bytes) -> tuple of Bytes
    Take in unprocessed meeting section and return a tuple containing processed meeting section 
    and L/T, which could either be ('L0101', 'L') or ('&nbsp;', '&').
    '''
    #print(sec)
    #sec = sec.lstrip('align=LEFT><font size =-1>')
   
    #sec = sec.rstrip('</font>')
    pat = re.compile('>(([PLT]\d{4})|(&nbsp;))<?')
    sec = re.search(pat, sec)
    return (sec.group(1), sec.group(1)[0:1])




def formatTimeLocInst(TLI):
    '''
    (Bytes) -> Bytes
    Take in unprocessed time, location and instructor and return processed time, location, and instructor.
    '''    
    #TLI = TLI.lstrip("align=LEFT>\s*?<font size =-1>")
    #TLI = TLI.rstrip("</font>")
    
    pat = re.compile('>([\w\s&;\-\.]+)<?')
    TLI = re.findall(pat, TLI)
    if TLI[0] == "&nbsp;":
        return "N/A"
    return TLI[0]


    

if __name__ == '__main__':
    
    # REMEMBER TO CHANGE DATABASE NAME AND TABLE NAME EACH TIME WE NEED TO UPDATE OUR CALENDAR INFO!!!
    # TO REPLACE ALL THE TABLE NAMES, USE EDIT -> SEARCH AND REPLACE -> REPLACE
    createDb = sqlite3.connect(':memory:')
    
    # Creates the SQLite cursor that is used to query the database
    cur = createDb.cursor() 
    cur.execute('''CREATE TABLE courses4(_id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT, url TEXT, FSY TEXT, meetSec TEXT, LT TEXT, time TEXT, loc TEXT, inst TEXT)''')
    
    storeCourseInfo(courseListings(), cur)
    storeSeminar(cur)
    
    cur.execute('SELECT * FROM courses4')
    for i in cur:
        print (i)
                
            
    createDb.commit()
    
    cur.close()